package com.lyq.demo.excel.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 读取解析Excel文件
 */
public class ExcelUtil {


    public static final String EXCEL_03_SUFFIX = ".xls";
    public static final String EXCEL_07_SUFFIX = ".xlsx";

    public static List<Map<String, String>> readExcel(MultipartFile file, List<String> keys) throws IOException {
        return readExcel(file.getInputStream(), file.getOriginalFilename(),keys);
    }

    /**
     * 读取表格
     *
     * @param is       文件输入流
     * @param fileName 文件名，用来获取文件后缀
     * @return
     * @throws IOException
     */
    public static List<Map<String, String>> readExcel(InputStream is, String fileName, List<String> keys) throws IOException {
        //根据输入流获取Excel对象
        Workbook wb = getWorkbook(is, fileName);
        //通过Excel对象解析文件
        List<Map<String, String>> list = getListMaps(wb,keys);
        wb.close();
        is.close();
        return list;
    }

    /**
     * 获取Excel解析对象
     *
     * @param is
     * @param fileName
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream is, String fileName) throws IOException {
        Workbook wb = null;
        String extString = fileName.substring(fileName.lastIndexOf("."));
        if (EXCEL_03_SUFFIX.equals(extString)) {
            wb = new HSSFWorkbook(is);
        } else if (EXCEL_07_SUFFIX.equals(extString)) {
            wb = new XSSFWorkbook(is);
        }
        return wb;
    }

    private static List<Map<String, String>> getListMaps(Workbook wb, List<String> keys) {
        List<Map<String, String>> list = new ArrayList<>();
        if (wb != null) {
            Sheet sheet = wb.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                Map<String,String> map = new HashMap<>();
                Row row = sheet.getRow(i);
                if (row != null) {
                    short lastCellNum = row.getLastCellNum();
                    for (short j = 0; j <= lastCellNum && j < keys.size(); j++) {
                        String cellValue = getCellFormatValue(row.getCell(j));
                        map.put(keys.get(j), cellValue);
                    }
                    list.add(map);
                }
            }
        }
        return list;
    }

    /**
     * 根据cell表格,列值的类型解析获取单元格值
     *
     * @param cell
     * @return
     */
    public static String getCellFormatValue(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    return df.format(date);
                }
            }
            cell.setCellType(CellType.STRING);
            cellValue = cell.getStringCellValue();

        }
        return cellValue;
    }
}
